 

/*********************************************************************
*
*      Copyright (C) 2002 Andrew Khan
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***************************************************************************/

 

import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.*;
import jxl.common.Logger;

import jxl.Cell;
import jxl.CellReferenceHelper;
import jxl.CellType;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Blank;
import jxl.write.DateFormat;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Demo class which uses the api to read in a spreadsheet and generate a clone
 * of that spreadsheet which contains the same data.  If the spreadsheet read
 * in is the spreadsheet called jxlrwtest.xls (provided with the distribution)
 * then this class will modify certain fields in the copy of that spreadsheet.
 * This is illustrating that it is possible to read in a spreadsheet, modify
 * a few values, and write it under a new name.
 */
public class ReadWrite
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(ReadWrite.class);

  /**
   * The spreadsheet to read in
   */
  private File inputWorkbook;
  /**
   * The spreadsheet to output
   */
  private File outputWorkbook;
  
  private ArrayList<Bon> bonnen;

  /**
   * Constructor
   * 
   * @param output 
   * @param input 
   */
  public ReadWrite( ArrayList<Bon> bonnen, Product p)
  {
    inputWorkbook = new File("excel/template.xls");
    outputWorkbook = new File("excel/" + p.getNaam() + ".xls");
    logger.setSuppressWarnings(Boolean.getBoolean("jxl.nowarnings"));
    logger.info("Input file: excel/template.xls" );    
    logger.info("Output file:  " + p.getNaam() + ".xls");
    this.bonnen = bonnen;
    try{
            readWrite(p);
        }
        catch (Throwable t)    {
            System.out.println(t.toString());
            t.printStackTrace();
        }
  }

  /**
   * Reads in the inputFile and creates a writable copy of it called outputFile
   * 
   * @exception IOException 
   * @exception BiffException 
   */
  public void readWrite(Product p) throws IOException, BiffException, WriteException
  {
    logger.info("Reading...");
    Workbook w1 = Workbook.getWorkbook(inputWorkbook);

    logger.info("Copying...");
    WritableWorkbook w2 = Workbook.createWorkbook(outputWorkbook, w1);
      modify(w2, p);
   

    w2.write();
    w2.close();
    logger.info("Done");
  }

  /**
   * If the inputFile was the test spreadsheet, then it modifies certain fields
   * of the writable copy
   * 
   * @param w 
   */
  private void modify(WritableWorkbook w, Product p ) throws WriteException
  {
    logger.info("Modifying...");



            String naam = p.getNaam();
            WritableSheet s = w.getSheet(0);
            s.setName(naam);
            writeNumberFormatSheet(s, p);
            
        
        
    
    }
    
    private void writeNumberFormatSheet(WritableSheet s, Product p) throws WriteException{
        int[] aantal;
        double[] prijs;
        int[] aantalPrijs;
        prijs = new double[12];
        aantalPrijs = new int[100];
        aantal = new int[12];
        WritableCellFormat wrappedText = new WritableCellFormat
            (WritableWorkbook.ARIAL_10_PT);
        wrappedText.setWrap(true);
    
        // Floats
        Label l = new Label(0,0,"Naam product:", wrappedText);
        s.addCell(l);
        l = new Label(0,1, p.getNaam(), wrappedText);
        s.addCell(l);
        l = new Label(0,2, "gemiddelde prijs: ", wrappedText);
        s.addCell(l);
        Number n = new Number(0, 1, 0 );

        // Lots of number 
        
        
        for(Bon bon : bonnen){
            for(Bonregel bonregel : bon.getBonregels() ){
                if(bonregel.getProduct().getNaam().equalsIgnoreCase(p.getNaam() )){
                    aantal[bon.getDatum().getMaand()-1] += bonregel.getAantal();
                    prijs[bon.getDatum().getMaand()-1] += bonregel.getPrijs();
                    aantalPrijs[bon.getDatum().getMaand()-1] += 1;
                }
            }
        }
        for(int i=0;i<40;i++){
              s.setColumnView(i,17);
        }
        double laatstePrijs = 0;
        int i = 0;
        while(laatstePrijs == 0 && i<12){
            if(aantalPrijs[i]!=0){
                laatstePrijs = ( (prijs[i]) / (aantalPrijs[i]) );
            }
            i++;
        }
        for (int col = 1; col < 13; col++){
            l = new Label(col, 0, "Maand " + col, wrappedText);
            s.addCell(l);
            n = new Number(col, 1, aantal[col-1]);
            s.addCell(n);
            if(aantalPrijs[col-1]!=0){
                double prijs1 = ( (prijs[col-1]) / (aantalPrijs[col-1]) );
                laatstePrijs = prijs1;
                n = new Number(col, 2,  prijs1);
            }else{
                
                n = new Number(col, 2,  laatstePrijs);
            }
            s.addCell(n);
        }
    }

}
